﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using TransportManager.UI.Pages;
using Site.Components;
using Microsoft.ApplicationBlocks.Data;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using System.Data;
using System.IO;
using Site.Components.Entity;

namespace Site.Report
{
    public partial class ProductSupplyByDepCodeAndProejctCode : BasePage
    {
        HSSFWorkbook hssfworkbook;
        private DataTable _GlobalDataTable;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                drpYear.GenerateYear(DateTime.Now.Year);
                drpMonth.GenerateMonth(DateTime.Now.Month);
            }
        }

        void BindData()
        {
            var ds = SqlHelper.ExecuteDataset(Session["Conn"].ToString(),
                "Porc_ProductSupplyByDepCodeAndProejctCode",CurrentDateTime);
            _GlobalDataTable = ds.Tables[0];
            rep.DataSource = ds;
            rep.DataBind();
            string statSql = "select sum(thisinmoney) thisin ,sum(thisstartmoney) thisstart,sum(thisoutmoney) thisout, round(sum(thisrestmoney) ,2) as thisrest from caiwuMonthreport where year(dates)=" + drpYear.SelectedValue + " and month(dates) =" + drpMonth.SelectedValue + "";

            var dt = SqlHelper.ExecuteDataset(Session["Conn"].ToString(), System.Data.CommandType.Text, statSql).Tables[0];
            repStat.DataSource = dt;
            repStat.DataBind();

        }

        void BindMergeData()
        {
            var ds = SqlHelper.ExecuteDataset(Session["Conn"].ToString(),
                "Porc_ProductSupplyByDepCodeAndProejctCode", CurrentDateTime);
            _GlobalDataTable = ds.Tables[0];


            rep.DataSource = ds.Tables[0].ToList<ProductOutStat>().MergeStat();
            rep.DataBind();
            string statSql = "select sum(thisinmoney) thisin ,sum(thisstartmoney) thisstart,sum(thisoutmoney) thisout, round(sum(thisrestmoney) ,2) as thisrest from caiwuMonthreport where year(dates)=" + drpYear.SelectedValue + " and month(dates) =" + drpMonth.SelectedValue + "";

            var dt = SqlHelper.ExecuteDataset(Session["Conn"].ToString(), System.Data.CommandType.Text, statSql).Tables[0];
            repStat.DataSource = dt;
            repStat.DataBind();

        }

        public DateTime CurrentDateTime
        {
            get
            {
                return DateTime.Parse(drpYear.SelectedValue + "-" + drpMonth.SelectedValue + "-1");
            }
        }

        protected void btnSearch_Click(object sender, EventArgs e)
        {
            BindData();
        }

        public double GetTotal(dynamic dv)
        {
            DataRow dr = null;
            if (dv is DataRow)
            {
                dr = dv;
            }
            else if (dv is DataRowView)
            {
                dr = dv.Row;
            }
            else
            {

            }           
            double add = 0d;
            if (dr != null)
            {
                foreach (DataColumn column in dr.Table.Columns)
                {
                    if (column.DataType == typeof(double) || column.DataType == typeof(float)
                        || column.DataType == typeof(int))
                    {

                        if (dr[column.ColumnName] != DBNull.Value)
                        {
                            if (column.ColumnName.ToLower() != "billnumber")
                            {
                                add += Convert.ToDouble(dr[column.ColumnName]);
                            }
                        }
                    }
                }
            }
            else
            {
                
                foreach (System.Reflection.PropertyInfo property  in dv.GetType().GetProperties())
                {
                    if (property.PropertyType == typeof(double) || property.PropertyType == typeof(float)
                        || property.PropertyType == typeof(int))
                    {


                        if (property.Name.ToLower() != "billnumber")
                            {
                                add += Convert.ToDouble(property.GetValue(dv,null));
                            }
                        
                    }
                }
            }
            return add;
            //return Convert.ToDouble(dr["金具"].ToString()) +
            //                     Convert.ToDouble(dr["线材"].ToString()) +
            //                    Convert.ToDouble(dr["塔材及加工件"].ToString()) +
            //                    Convert.ToDouble(dr["钢材"].ToString()) +
            //                    Convert.ToDouble(dr["绝缘材料"].ToString()) +
            //                    Convert.ToDouble(dr["主要设备"].ToString()) +
            //                    Convert.ToDouble(dr["硬母线"].ToString()) +
            //                    Convert.ToDouble(dr["电缆桥支架槽盒"].ToString()) +
            //                    Convert.ToDouble(dr["防火材料"].ToString()) +
            //                    Convert.ToDouble(dr["降阻剂"].ToString());
                                 
        }

        protected void btnExport_Click(object sender, EventArgs e)
        {
            string filename = string.Format("供应总计（材料出库统计表）.xls", drpYear.SelectedValue, drpMonth.SelectedValue);
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", HttpUtility.UrlEncode(filename,System.Text.Encoding.UTF8)));
            Response.Clear();


            FileStream fstream = new FileStream(MapPath("~/Template/ProductSupplyByDepCodeAndProejctCodetemplate.xls"), FileMode.Open, FileAccess.Read);
            var hssfworkbook = new HSSFWorkbook(fstream);
            var sheet = hssfworkbook.GetSheetAt(0);



            var ds = SqlHelper.ExecuteDataset(Session["Conn"].ToString(),
            "Porc_ProductSupplyByDepCodeAndProejctCode", CurrentDateTime);
          
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                var row = sheet.CreateRow(i + 2);
              
                var dr= ds.Tables[0].Rows[i];
                row.CreateCell(0).SetCellValue(getFenName(dr["abstract"].ToString()));
                row.CreateCell(1).SetCellValue(dr["name"].ToString());
                row.CreateCell(2).SetCellValue(dr["金具"].ToString());
                row.CreateCell(3).SetCellValue(dr["线材"].ToString());
                row.CreateCell(4).SetCellValue(dr["塔材及加工件"].ToString());
                row.CreateCell(5).SetCellValue(dr["钢材"].ToString());
                row.CreateCell(6).SetCellValue(dr["绝缘材料"].ToString());
                row.CreateCell(7).SetCellValue(dr["主要设备"].ToString());
                row.CreateCell(8).SetCellValue(dr["硬母线"].ToString());
                row.CreateCell(9).SetCellValue(dr["电缆桥支架槽盒"].ToString());
                row.CreateCell(10).SetCellValue(dr["防火材料"].ToString());
                row.CreateCell(11).SetCellValue(dr["降阻剂"].ToString());
                //其他
                double othertotal = Convert.ToDouble(dr["耗材"]) + Convert.ToDouble(dr["工器具"]) + Convert.ToDouble(dr["汽配"]);
                row.CreateCell(12).SetCellValue(dr["耗材"].ToString());
                row.CreateCell(13).SetCellValue(dr["工器具"].ToString());
                row.CreateCell(14).SetCellValue(dr["汽配"].ToString());
                //合计
                row.CreateCell(15).SetCellValue(GetTotal(dr));
              
               
            }
            string statSql = "select sum(thisinmoney) thisin ,sum(thisstartmoney) thisstart,sum(thisoutmoney) thisout, sum(thisrestmoney) thisrest from caiwuMonthreport where corpcode='5000' and  year(dates)=" + drpYear.SelectedValue + " and month(dates) =" + drpMonth.SelectedValue + "";
            var dt = SqlHelper.ExecuteDataset(Session["Conn"].ToString(), System.Data.CommandType.Text, statSql).Tables[0];
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                int start = ds.Tables[0].Rows.Count + 2;
                var row = sheet.CreateRow(start+j);
                var dr = dt.Rows[j];
                
                    row.CreateCell(0).SetCellValue("本月验收");
                    row.CreateCell(1).SetCellValue(dr["thisin"].ToString());

                    row = sheet.CreateRow(start + j+1);
               
                    row.CreateCell(0).SetCellValue("上月结余");
                    row.CreateCell(1).SetCellValue(dr["thisstart"].ToString());

                    row = sheet.CreateRow(start + j + 2);
                    row.CreateCell(0).SetCellValue("本月出库");
                    row.CreateCell(1).SetCellValue(dr["thisout"].ToString());

                    row = sheet.CreateRow(start + j + 3);
                    row.CreateCell(0).SetCellValue("本月结余");
                    row.CreateCell(1).SetCellValue(dr["thisrest"].ToString());
               
            }

            MemoryStream ms = new MemoryStream();
            hssfworkbook.Write(ms);
            Response.BinaryWrite(ms.GetBuffer());
            Response.End();
        }

        protected void rep_ItemDataBound(object sender, RepeaterItemEventArgs e)
        {
            if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
            {
                Label lblTotal = e.Item.FindControl("lblTotal") as Label;
                if(rep.DataSource is IList<ProductOutStat>)
                {
                    lblTotal.Text = GetTotal(e.Item.DataItem).ToString();
                }
                else
                {
                    var dr = (DataRowView)e.Item.DataItem;

                    lblTotal.Text = GetTotal(dr).ToString();
                }
            }
        }

        protected void btnMergeSearch_Click(object sender, EventArgs e)
        {
            BindMergeData();
        }

        protected void btnMergeExport_Click(object sender, EventArgs e)
        {
            string filename = string.Format("供应总计（材料出库统计表）.xls", drpYear.SelectedValue, drpMonth.SelectedValue);
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8)));
            Response.Clear();


            FileStream fstream = new FileStream(MapPath("~/Template/ProductSupplyByDepCodeAndProejctCodetemplate.xls"), FileMode.Open, FileAccess.Read);
            var hssfworkbook = new HSSFWorkbook(fstream);
            var sheet = hssfworkbook.GetSheetAt(0);



            var ds = SqlHelper.ExecuteDataset(Session["Conn"].ToString(),
            "Porc_ProductSupplyByDepCodeAndProejctCode", CurrentDateTime);
            var list = ds.Tables[0].ToList<ProductOutStat>().MergeStat();
            for (int i = 0; i < list.Count; i++)
            {
                var row = sheet.CreateRow(i + 2);

                var dr = list[i];
                row.CreateCell(0).SetCellValue(getFenName(dr.Abstract.ToString()));
                row.CreateCell(1).SetCellValue(dr.Name.ToString());
                row.CreateCell(2).SetCellValue(dr.金具.ToString());
                row.CreateCell(3).SetCellValue(dr.线材.ToString());
                row.CreateCell(4).SetCellValue(dr.塔材及加工件.ToString());
                row.CreateCell(5).SetCellValue(dr.钢材.ToString());
                row.CreateCell(6).SetCellValue(dr.绝缘材料.ToString());
                row.CreateCell(7).SetCellValue(dr.主要设备.ToString());
                row.CreateCell(8).SetCellValue(dr.硬母线.ToString());
                row.CreateCell(9).SetCellValue(dr.电缆桥支架槽盒.ToString());
                row.CreateCell(10).SetCellValue(dr.防火材料.ToString());
                row.CreateCell(11).SetCellValue(dr.降阻剂.ToString());
                //其他
                double othertotal = Convert.ToDouble(dr.耗材) + Convert.ToDouble(dr.工器具) + Convert.ToDouble(dr.汽配);
                row.CreateCell(12).SetCellValue(dr.耗材.ToString());
                row.CreateCell(13).SetCellValue(dr.工器具.ToString());
                row.CreateCell(14).SetCellValue(dr.汽配.ToString());
                //合计
                row.CreateCell(15).SetCellValue(GetTotal(dr));
                row.CreateCell(16).SetCellValue(dr.Billnumber);
            }
            string statSql = "select sum(thisinmoney) thisin ,sum(thisstartmoney) thisstart,sum(thisoutmoney) thisout, sum(thisrestmoney) thisrest from caiwuMonthreport where corpcode='5000' and  year(dates)=" + drpYear.SelectedValue + " and month(dates) =" + drpMonth.SelectedValue + "";
            var dt = SqlHelper.ExecuteDataset(Session["Conn"].ToString(), System.Data.CommandType.Text, statSql).Tables[0];
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                int start = ds.Tables[0].Rows.Count + 2;
                var row = sheet.CreateRow(start + j);
                var dr = dt.Rows[j];

                row.CreateCell(0).SetCellValue("本月验收");
                row.CreateCell(1).SetCellValue(dr["thisin"].ToString());

                row = sheet.CreateRow(start + j + 1);

                row.CreateCell(0).SetCellValue("上月结余");
                row.CreateCell(1).SetCellValue(dr["thisstart"].ToString());

                row = sheet.CreateRow(start + j + 2);
                row.CreateCell(0).SetCellValue("本月出库");
                row.CreateCell(1).SetCellValue(dr["thisout"].ToString());

                row = sheet.CreateRow(start + j + 3);
                row.CreateCell(0).SetCellValue("本月结余");
                row.CreateCell(1).SetCellValue(dr["thisrest"].ToString());

            }

            MemoryStream ms = new MemoryStream();
            hssfworkbook.Write(ms);
            Response.BinaryWrite(ms.GetBuffer());
            Response.End();
        } 


        
    }
}